use "${data}/source/PatFR_Match_sireninfo_with_appln_sectorclean", clear
	keep if inrange(cate, 5000, 5999)
	merge m:1 siren using "${data}/Utils/SirenApet_maineng_consolidate", nogen keep(1 3)
	merge m:1 siren using "${data}/Utils/SirenApet_maineng_noconsolidate", nogen update keep(1 3 4 5)
	replace ape = apet if !mi(apet)
	drop apet
	//patch for main missing APE
	replace ape = "2120Z" if siren == 732059332 //SANOFI
	replace ape = "2016Z" if siren == 445115611 //ARKEMA
	replace ape = "2120Z" if siren == 306094053 //MODAX
	replace ape = "2630Z" if siren == 307593129 //MITSUBISHI ELECTRIC TELECOM
	replace ape = "2120Z" if siren == 310635248 //CELLTECH PHARMA
	replace ape = "2670Z" if siren == 314899972 //NETTEST PHOTONICS
	replace ape = "0113Z" if siren == 321628950 //LIMAGRAIN GENETICS
	replace ape = "8299Z" if siren == 322593104 //SETVAL
	replace ape = "7112B" if siren == 324502194 //SORAPEC
	replace ape = "2651B" if siren == 327665865 //SINTERS
	replace ape = "3220C" if siren == 340818921 //SEDIA
	keep siren appln_id ape earliest
	duplicates drop
	drop siren
	drop if ape == ""
	keep appln_id  ape earliest
save "${tmp}/appln_id_ape", replace
insheet using "${data}/source/202107_OECD_PATENT_QUALITY_EPO_INDIC_rev.txt", delimiter("|") clear 
save "${tmp}/appln_id_quals", replace

**

use "${data}/source/EPO_2021_geocoded_withsector", clear
	gen year = floor(publication_date / 10000)
	keep appln_id  ZE2010 year ape
	keep appln_id ZE  year ape
	
	rename ape ape_old
	joinby appln_id using "${tmp}/appln_id_ape", unmatched(master)
	replace ape = ape_old if ape == ""
	drop ape_old _merge
	drop if ape == ""
	replace year = year(earliest) if earliest != .
	bys appln_id: gen den = _N
	bys appln_id ape ZE: gen num = _N
	gen share = num  /den
	replace share= 1
	duplicates drop
	
	merge m:1  appln_id using "${tmp}/appln_id_quals"

	
	bys ZE ape year: egen tot_nplcits = sum(npl_cits)
	bys ZE ape year: egen mean_nplcits = mean(npl_cits)
	drop npl_cits	
	bys ZE ape year: egen tot_patcits = sum(fwd_cits5_xy)
	bys ZE ape year: egen mean_patcits = mean(fwd_cits5_xy)
	drop fwd_cits5
	bys ZE ape year: egen tot_claims = sum(claims)
	egen foo = median(quality_index_4)
	bys ZE ape year: egen tot_qual = sum(quality_index_4>foo)
	gen granted = (grant_lag != .)
	bys ZE ape year: egen tot_granted = sum(granted)
	
	
	bys tech_field year: gen syf = _N
	bys year: gen sy = _N
	bys tech_field: gen sf = _N
	bys ZE ape year: egen pat_nonorm = sum(share)
	bys ZE ape year: egen pat_normyf = sum(share/syf)
	bys ZE ape year: egen pat_normy = sum(share/sy)
	bys ZE ape year: egen pat_normf = sum(share/sf)

	keep ZE ape year pat_nonorm tot_nplcits mean_nplcits tot_patcits mean_patcits tot_claims tot_qual tot_granted
	gduplicates drop
*	tostring ZE2010, replace 
*	replace ZE2010 = "0"+ZE2010 if length(ZE2010) == 3
*	replace ZE2010 = "0"+ZE2010 if length(ZE2010) == 3
	
save "${tmp}/total_patent_ZE_ape.dta", replace
	
	